package zy.dao.sys.role.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sys.role.RoleDAO;
import zy.entity.sys.menu.T_Sys_Menu;
import zy.entity.sys.role.T_Sys_Role;
import zy.entity.sys.role.T_Sys_RoleMenu;
import zy.util.CommonUtil;
@Repository
public class RoleDAOImpl extends BaseDaoImpl implements RoleDAO{

	@Override
	public List<T_Sys_Role> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ro_id,ro_code,ro_name,ty_name");
		sql.append(" from t_sys_role t");
		sql.append(" join common_type s");
		sql.append(" on ty_id=ro_shop_type");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.ro_name,:name)>0");
        }
        if(null != type && !"".equals(type)){
        	sql.append(" and t.ro_shop_type=:shop_type");
        }
        sql.append(" and t.ro_shop_code=:shop_code");
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by ro_id desc");
		List<T_Sys_Role> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sys_Role.class));
		return list;
	}

	@Override
	public List<T_Sys_Role> dia_list(Map<String, Object> param) {
		Object name = param.get("name");
		Object type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ro_id,ro_code,ro_shop_type,ro_name,ty_name");
		sql.append(" from t_sys_role t");
		sql.append(" join common_type s");
		sql.append(" on ty_id=ro_shop_type");
		if(null != type && !"".equals(type)){
			sql.append(" and instr(s.ty_state,:shop_type)>0");
		}
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.ro_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by ro_id desc");
		List<T_Sys_Role> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sys_Role.class));
		return list;
	}

	@Override
	public Integer queryByName(T_Sys_Role model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ro_id from T_Sys_Role");
		sql.append(" where 1=1");
		if(null != model.getRo_name() && !"".equals(model.getRo_name())){
			sql.append(" and ro_name=:ro_name");
		}
		if(null != model.getRo_id() && model.getRo_id() > 0){
			sql.append(" and ro_id <> :ro_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model) ,Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public T_Sys_Role queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ro_id,ro_code,ro_name,ro_shop_type,ty_name");
		sql.append(" from t_sys_role t");
		sql.append(" join common_type s");
		sql.append(" on ty_id=ro_shop_type");
		sql.append(" where ro_id=:ro_id");
		try{
			T_Sys_Role data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ro_id", id),new BeanPropertyRowMapper<>(T_Sys_Role.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void update(T_Sys_Role model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update T_Sys_Role");
		sql.append(" set ro_name=:ro_name");
		sql.append(" where ro_id=:ro_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void save(T_Sys_Role model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(ro_code+0)) from T_Sys_Role ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setRo_code(code);
		sql.setLength(0);
		
		sql.append("INSERT INTO T_Sys_Role");
		sql.append(" (ro_code,ro_name,ro_shop_type,ro_date,ro_shop_code,companyid)");
		sql.append(" VALUES(:ro_code,:ro_name,:ro_shop_type,:ro_date,:ro_shop_code,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model),holder);
		int id = holder.getKey().intValue();
		model.setRo_id(id);
	}

	@Override
	public void del(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM T_Sys_Role");
		sql.append(" WHERE ro_id=:ro_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ro_id", id));
	}

	@Override
	public List<T_Sys_Menu> listMenu(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ro_shop_type");
		sql.append(" FROM t_sys_role");
		sql.append(" WHERE ro_code=:ro_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		Integer type = -1;
		try{
			type = namedParameterJdbcTemplate.queryForObject(sql.toString(),param,Integer.class);
		}catch(Exception e){
		}
		sql.setLength(0);
		sql.append("SELECT rm_id as mn_id,rm_ro_code as ro_code,rm_state as mn_state,rm_limit,mn_code,mn_name,mn_upcode,mn_child,");
		if(1 == type){
			sql.append("mn_limit");
		}
		if(2 == type){
			sql.append("mn_area_limit AS mn_limit");
		}
		if(3 == type){
			sql.append("mn_own_limit AS mn_limit");
		}
		if(4 == type){
			sql.append("mn_join_limit AS mn_limit");
		}
		if(5 == type){
			sql.append("mn_team_limit AS mn_limit");
		}
		sql.append(" FROM t_sys_rolemenu rm");
		sql.append(" JOIN t_sys_menu ON mn_code = rm_mn_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND rm_ro_code=:ro_code");
		sql.append(" AND mn_state=0");
		sql.append(" AND rm.companyid=:companyid");
		sql.append(" ORDER BY mn_code,mn_order");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sys_Menu.class));
	}

	@Override
	public void updateMenus(List<T_Sys_Menu> menuList) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sys_rolemenu");
		sql.append(" SET rm_state=:mn_state");
		sql.append(" ,rm_limit=:rm_limit");
		sql.append(" WHERE rm_id=:mn_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(menuList.toArray()));
	}

	@Override
	public void updateMenu(T_Sys_Menu menu) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sys_rolemenu");
		sql.append(" SET rm_state=:mn_state");
		sql.append(" ,rm_limit=:rm_limit");
		sql.append(" WHERE rm_id=:mn_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(menu));
	}

	@Override
	public List<T_Sys_Menu> listAllMenu(String mn_version,Integer shop_type){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mn_id,mn_code,mn_name,mn_upcode,mn_url,mn_state,mn_order,mn_version,mn_shop_type,");
		if(1 == shop_type.intValue()){
			sql.append("mn_limit");
		}else if(2 == shop_type.intValue()){
			sql.append("mn_area_limit AS mn_limit");
		}else if(3 == shop_type.intValue()){
			sql.append("mn_own_limit AS mn_limit");
		}else if(4 == shop_type.intValue()){
			sql.append("mn_join_limit AS mn_limit");
		}else if(5 == shop_type.intValue()){
			sql.append("mn_team_limit AS mn_limit");
		}
		sql.append(" FROM t_sys_menu");
		sql.append(" WHERE 1=1");
		sql.append(" AND mn_state = 0");
		sql.append(" AND INSTR(mn_version,:mn_version)>0");
		sql.append(" AND INSTR(mn_shop_type,:shop_type)>0");
		sql.append(" ORDER BY mn_code,mn_order");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("mn_version", mn_version).addValue("shop_type", shop_type), 
				new BeanPropertyRowMapper<>(T_Sys_Menu.class));
	}
	
	@Override
	public List<T_Sys_RoleMenu> listRoleMenu(String ro_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT rm_id,rm_ro_code,rm_state,rm_limit,rm_mn_code,companyid");
		sql.append(" FROM t_sys_rolemenu");
		sql.append(" WHERE rm_ro_code = :ro_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("ro_code", ro_code).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sys_RoleMenu.class));
	}
	
	@Override
	public void saveRoleMenu(List<T_Sys_RoleMenu> roleMenus) {
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO t_sys_rolemenu");
		sql.append(" (rm_ro_code,rm_state,rm_limit,rm_mn_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:rm_ro_code,:rm_state,:rm_limit,:rm_mn_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(roleMenus.toArray()));
	}
	
	@Override
	public void delRoleMenu(List<Integer> rm_ids) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM t_sys_rolemenu");
		sql.append(" WHERE rm_id IN (:rm_ids)");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("rm_ids", rm_ids));
	}
	
}
